package com.projectName.www.dao;

import com.projectName.www.util.JDBCUtils;
import com.projectName.www.util.MD5Util;
import com.projectName.www.po.User;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    User user = null;
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    @Override
    public User queryByUsername(String username) {

        try {
            connection = JDBCUtils.getConnection();//首先连接数据库

            String sql = "select * from account where userName=?";//写出sql语句

            preparedStatement = connection.prepareStatement(sql);//执行sql语句

            preparedStatement.setString(1,username);//为sql语句中的变量赋值

            resultSet = preparedStatement.executeQuery();//获得查询结果集

            if (resultSet.next()){

                user = new User();

                user.setMoney(resultSet.getBigDecimal("money"));

                user.setuId(Integer.parseInt(resultSet.getString("uid")));//将数据库中取出来的id字段字符类型转换为整型并赋给user对象

                user.setPassword(resultSet.getString("userPwd"));

                user.setUserName(resultSet.getString("userName"));

                user.setUserType(resultSet.getInt("userType"));

                user.setJianjie(resultSet.getString("jianjie"));

                user.setUserType(resultSet.getInt("userType"));

                user.setAddress(resultSet.getString("address"));

                user.setContact(resultSet.getString("contact"));

                user.setOnlinestatus(resultSet.getInt("onlinestatus"));

                user.setDiscount(resultSet.getBigDecimal("discount"));
            }

        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
    } finally {
        JDBCUtils.close(resultSet,preparedStatement,connection);
    }
        return user;
    }

    @Override
    public void insert(User user) {
        try {
            connection = JDBCUtils.getConnection();//首先连接数据库

            String sql = "INSERT INTO account (userName,userPwd,userType,contact,address,jianjie,onlinestatus) VALUES (?,?,?,?,?,?,?)";//写出sql语句

            preparedStatement = connection.prepareStatement(sql);//执行sql语句

            preparedStatement.setString(1,user.getUserName());//为sql语句中的变量赋值
            preparedStatement.setString(2, user.getPassword());//为sql语句中的变量赋值
            preparedStatement.setInt(3,user.getUserType());//为sql语句中的变量赋值
            preparedStatement.setString(4,user.getContact());
            preparedStatement.setString(5,user.getAddress());
            preparedStatement.setString(6,user.getJianjie());
            preparedStatement.setInt(7,user.getOnlinestatus());

            preparedStatement.executeUpdate();


        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
    }

    @Override
    public void update(User user) {
        try {
            connection = JDBCUtils.getConnection();//首先连接数据库

            //改变姓名，联系方式，地址，简介
            String sql = "update account set userName=?,userPwd=?,contact=?,address=?,jianjie=?,userType=?,onlinestatus=?,money=? " +
                    "WHERE uid=?";//写出sql语句

            preparedStatement = connection.prepareStatement(sql);//执行sql语句


            preparedStatement.setString(1,user.getUserName());//为sql语句中的变量赋值
            preparedStatement.setString(2,user.getPassword());
            preparedStatement.setString(3,user.getContact());//为sql语句中的变量赋值
            preparedStatement.setString(4,user.getAddress());//为sql语句中的变量赋值
            preparedStatement.setString(5,user.getJianjie());//为sql语句中的变量赋值
            preparedStatement.setInt(6,user.getUserType());//为sql语句中的变量赋值
            preparedStatement.setInt(7,user.getOnlinestatus());//为sql语句中的变量赋值
            preparedStatement.setBigDecimal(8,user.getMoney());
            preparedStatement.setInt(9,user.getuId());

            preparedStatement.executeUpdate();


        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
    }


    @Override
    public List<User> findall() {
        List<User>userList=new ArrayList<>();
        try {

            connection = JDBCUtils.getConnection();//首先连接数据库

            String sql = "select * from account ";//写出sql语句

            preparedStatement = connection.prepareStatement(sql);//执行sql语句


            resultSet = preparedStatement.executeQuery();//获得查询结果集



            while (resultSet.next()){

                user = new User();

                user.setuId(Integer.parseInt(resultSet.getString("uid")));//将数据库中取出来的id字段字符类型转换为整型并赋给user对象

                user.setMoney(resultSet.getBigDecimal("money"));

                user.setPassword(resultSet.getString("userPwd"));

                user.setUserName(resultSet.getString("userName"));

                user.setUserType(resultSet.getInt("userType"));

                user.setJianjie(resultSet.getString("jianjie"));

                user.setUserType(resultSet.getInt("userType"));

                user.setAddress(resultSet.getString("address"));

                user.setContact(resultSet.getString("contact"));

                user.setOnlinestatus(resultSet.getInt("onlinestatus"));

                user.setDiscount(resultSet.getBigDecimal("discount"));

                userList.add(user);
            }

        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
        return userList;

//        /**
//         * 优化代码：查询结果集
//         */
//        try
//        {
//            //创建map来接收结果集
//            //创建连接
//            connection = JDBCUtils.getConnection();
//            //定义sql
//            StringBuilder sql = new StringBuilder()
//                    .append("SELECT *")
//                    .append("  FROM account");
//            //预编译sql
//            preparedStatement = connection.prepareStatement(sql.toString());
//            //执行sql
//            rs = preparedStatement.executeQuery();
//            //定义用于装载的Map容器
//            Map<String,String> cup = null;
//            //判断是否查询到数据
//            while(rs.next()) {
//                //创建结果集描述对象
//                ResultSetMetaData rsmd = resultSet.getMetaData();
//                //计算列数
//                int column = rsmd.getColumnCount();
//                //计算安全容量
//                int size = ((int) (column / 0.75)) + 1;
//                //实例化Map容器
//                cup = new HashMap<>(size);
//                //循环所有列
//                for (int i = 1; i <= column; i++) {
//                    //完成列级映射
//                    cup.put(rsmd.getColumnLabel(i), resultSet.getString(i));
//
//                }
//            }
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        } finally
//        {
//            JDBCUtils.close(resultSet,preparedStatement,connection);
//        }
//        return userList;


    }

    @Override
    public User findByID(String uid) {
        try {
            connection = JDBCUtils.getConnection();//首先连接数据库

            String sql = "select * from account where uid=? ";//写出sql语句

            preparedStatement = connection.prepareStatement(sql);//执行sql语句

            preparedStatement.setString(1,uid);//为sql语句中的变量赋值

            resultSet = preparedStatement.executeQuery();//获得查询结果集


            if (resultSet.next()){

                user = new User();

                user.setuId(Integer.parseInt(resultSet.getString("uid")));//将数据库中取出来的id字段字符类型转换为整型并赋给user对象

                user.setMoney(resultSet.getBigDecimal("money"));

                user.setPassword(resultSet.getString("userPwd"));

                user.setUserName(resultSet.getString("userName"));

                user.setUserType(resultSet.getInt("userType"));

                user.setJianjie(resultSet.getString("jianjie"));

                user.setUserType(resultSet.getInt("userType"));

                user.setAddress(resultSet.getString("address"));

                user.setContact(resultSet.getString("contact"));

                user.setOnlinestatus(resultSet.getInt("onlinestatus"));

                user.setDiscount(resultSet.getBigDecimal("discount"));
            }

        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
        return user;
    }

    @Override
    public void delete(String uid) {

        try {
            connection = JDBCUtils.getConnection();//首先连接数据库

            String sql = "DELETE FROM account WHERE uid=?";//写出sql语句

            preparedStatement = connection.prepareStatement(sql);//执行sql语句

            preparedStatement.setString(1,uid);//为sql语句中的变量赋值

            preparedStatement.executeUpdate();//操作

        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }

    }

    //寻找卖家用户
    @Override
    public List<User> findSlr() {
        List<User>userList=new ArrayList<>();
        try {
            connection = JDBCUtils.getConnection();//首先连接数据库

            String sql = "select * from account where userType= 2 ";//写出sql语句

            preparedStatement = connection.prepareStatement(sql);//执行sql语句

            resultSet = preparedStatement.executeQuery();//获得查询结果集


            while (resultSet.next()){

                user = new User();

                user.setuId(Integer.parseInt(resultSet.getString("uid")));//将数据库中取出来的id字段字符类型转换为整型并赋给user对象

                user.setMoney(resultSet.getBigDecimal("money"));

                user.setPassword(resultSet.getString("userPwd"));

                user.setUserName(resultSet.getString("userName"));

                user.setUserType(resultSet.getInt("userType"));

                user.setJianjie(resultSet.getString("jianjie"));

                user.setUserType(resultSet.getInt("userType"));

                user.setAddress(resultSet.getString("address"));

                user.setContact(resultSet.getString("contact"));

                user.setOnlinestatus(resultSet.getInt("onlinestatus"));

                user.setDiscount(resultSet.getBigDecimal("discount"));

                userList.add(user);
            }

        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
        return userList;
    }

    @Override
    public void setonlineStatus(String uid,boolean status) {

        User user = this.findByID(uid);
        if(status)
        {
            user.setOnlinestatus(1);
        }else{
            user.setOnlinestatus(0);
        }
        this.update(user);
    }

    @Override
    public List<User> findUserByName(String userName) {
        List<User> userList=new ArrayList<>();
        try {
            connection = JDBCUtils.getConnection();//首先连接数据库

            String sql = "select * from account where userName like ?";//写出sql语句


            preparedStatement = connection.prepareStatement(sql);//执行sql语句

            userName='%'+userName+'%';//模糊查询

            preparedStatement.setString(1,userName);//为sql语句中的变量赋值

            resultSet = preparedStatement.executeQuery();//获得查询结果集

            while (resultSet.next()){

                user = new User();


                user.setuId(Integer.parseInt(resultSet.getString("uid")));//将数据库中取出来的id字段字符类型转换为整型并赋给user对象

                user.setMoney(resultSet.getBigDecimal("money"));

                user.setPassword(resultSet.getString("userPwd"));

                user.setUserName(resultSet.getString("userName"));

                user.setUserType(resultSet.getInt("userType"));

                user.setJianjie(resultSet.getString("jianjie"));

                user.setUserType(resultSet.getInt("userType"));

                user.setAddress(resultSet.getString("address"));

                user.setContact(resultSet.getString("contact"));

                user.setOnlinestatus(resultSet.getInt("onlinestatus"));

                user.setDiscount(resultSet.getBigDecimal("discount"));

                userList.add(user);
            }

        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }



        return userList;
    }

    @Override
    public void addMoney(String uid, BigDecimal admoney) {
        User user=this.findByID(uid);
        if(user!=null){
            user.setMoney(user.getMoney().add(admoney));
            this.update(user);
        }


    }

    @Override
    public BigDecimal getMoney(String uid) {
        User user=this.findByID(uid);
        if(user!=null){
            return user.getMoney();
        }
        return null;
    }


}
